"""
Case Type   : 防篡改
Case Name   : 账本数据库基础语法适配(DDL/DML)--ALTER 防篡改表
Create At   : 2022-02-28
Owner       : lwx1080719
Description :
    1.查询三权分立参数enableseparationofduty
    2.创建不同权限用户
    3.系统管理员创建schema并指定防篡改选项
    4.普通用户在防篡改schema下创建防篡改表
    5.普通用户修改防篡改表的owner
    6.系统管理员修改防篡改表的owner
    7.审计用户查询表
    8.普通用户查询表
    9.系统管理员修改防篡改schema的owner
    10.审计用户查询表
    11.审计管理员用户查看用户历史表
    12.审计管理员用户查看全局区块表
    14.系统管理员创建tablespace
    15.普通用户修改防篡改表的tablespace
    16.清理环境
Expect      :
    1.显示默认值off
    2.成功
    3.成功
    4.成功
    5.失败
    6.成功
    7.失败
    8.失败
    9.成功
    10.成功
    11.成功
    12.成功
    13.成功
    14.成功
    15.失败
    16.成功
History     :
"""




import os
import unittest
from yat.test import macro
from testcase.utils.Common import Common
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class ModifyCase(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.Constant = Constant()
        self.common = Common()
        self.schema_name = 's_security_0028'
        self.table_name = 't_security_0028'
        self.ts_name = 'ts_security_0028'
        self.user_name = 'u_security_tamper_proof'
        self.user_privileges = {f'{self.user_name}_sys': 'sysadmin',
                                f'{self.user_name}_audit': 'auditadmin',
                                f'{self.user_name}_noruser': ''}
        self.default_value = self.common.show_param('enableSeparationOfDuty')

    def test_security(self):
        text = '----step1:查询三权分立参数enableSeparationOfDuty值  ' \
               'expect:默认值off----'
        self.logger.info(text)
        self.assertEqual("off", self.default_value, "执行失败:" + text)

        text = '----step2:创建不同权限用户 expect:成功----'
        self.logger.info(text)
        for user, privilege in self.user_privileges.items():
            create_cmd = f"drop user if exists {user} cascade; " \
                f"create user {user} {privilege} " \
                f"password '{macro.PASSWD_REPLACE}' ; "
            msg = self.primary_sh.execut_db_sql(create_cmd)
            self.logger.info(msg)
            self.assertIn(self.Constant.CREATE_ROLE_SUCCESS_MSG, msg,
                          '执行失败:' + text)

        text = '----step3:系统管理员创建schema并指定防篡改选项 expect:成功----'
        self.logger.info(text)
        sql_cmd = f'''
            create schema {self.schema_name} authorization 
            {self.user_name}_noruser with blockchain;'''
        self.logger.info(sql_cmd)
        msg = self.primary_sh.execut_db_sql(
            sql_cmd, sql_type=f' -U {self.user_name}_sys '
            f'-W {macro.PASSWD_REPLACE}')
        self.logger.info(msg)
        self.assertIn(self.Constant.CREATE_SCHEMA_SUCCESS_MSG, msg,
                      '执行失败:' + text)

        text = '----step4:普通用户在防篡改schema下创建防篡改分区表' \
               'expect:成功----'
        self.logger.info(text)
        sql_cmd = f'''
            create table {self.schema_name}.{self.table_name}
            (id int primary key ,name varchar(20) not null ,
            address text default 'testui');'''
        self.logger.info(sql_cmd)
        msg = self.primary_sh.execut_db_sql(
            sql_cmd, sql_type=f' -U {self.user_name}_noruser '
            f'-W {macro.PASSWD_REPLACE}')
        self.logger.info(msg)
        self.assertIn(self.Constant.CREATE_TABLE_SUCCESS, msg,
                      "执行失败:" + text)

        text = '----step5:普通用户修改防篡改表的owner ' \
               'expect:失败----'
        self.logger.info(text)
        sql_cmd = f'''
            alter table {self.schema_name}.{self.table_name} 
            owner to {self.user_name}_audit;'''
        self.logger.info(sql_cmd)
        msg = self.primary_sh.execut_db_sql(
            sql_cmd, sql_type=f' -U {self.user_name}_noruser '
            f'-W {macro.PASSWD_REPLACE}')
        self.logger.info(msg)
        self.assertIn('must be member of role', msg, "执行失败:" + text)

        text = '----step6:系统管理员修改防篡改表的owner ' \
               'expect:成功----'
        self.logger.info(text)
        sql_cmd = f'''
                alter table {self.schema_name}.{self.table_name} 
                owner to {self.user_name}_audit;'''
        self.logger.info(sql_cmd)
        msg = self.primary_sh.execut_db_sql(sql_cmd)
        self.logger.info(msg)
        self.assertIn(self.Constant.ALTER_TABLE_MSG, msg,
                      "执行失败:" + text)

        text = '----step7:审计管理员用户查看表 expect:失败----'
        self.logger.info(text)
        sql_cmd = f'''select * from {self.schema_name}.{self.table_name};'''
        self.logger.info(sql_cmd)
        msg = self.primary_sh.execut_db_sql(
            sql_cmd, sql_type=f' -U {self.user_name}_audit '
            f'-W {macro.PASSWD_REPLACE}')
        self.logger.info(msg)
        self.assertIn(self.Constant.PERMISSION_DENIED, msg,
                         "执行失败:" + text)

        text = '----step8:普通用户查看表 expect:失败----'
        self.logger.info(text)
        sql_cmd = f'''select * from {self.schema_name}.{self.table_name};'''
        self.logger.info(sql_cmd)
        msg = self.primary_sh.execut_db_sql(
            sql_cmd, sql_type=f' -U {self.user_name}_noruser '
            f'-W {macro.PASSWD_REPLACE}')
        self.logger.info(msg)
        self.assertIn(self.Constant.PERMISSION_DENIED, msg,
                         "执行失败:" + text)

        text = '----step9:系统管理员修改防篡改schema的owner; ' \
               'expect:成功----'
        self.logger.info(text)
        sql_cmd = f'''
            alter schema {self.schema_name} owner to {self.user_name}_audit;'''
        self.logger.info(sql_cmd)
        msg = self.primary_sh.execut_db_sql(sql_cmd)
        self.logger.info(msg)
        self.assertIn(self.Constant.ALTER_SCHEMA_SUCCESS_MSG, msg,
                         "执行失败:" + text)

        text = '----step10:审计管理员用户查看表 expect:成功----'
        self.logger.info(text)
        sql_cmd = f'''select * from {self.schema_name}.{self.table_name};'''
        self.logger.info(sql_cmd)
        msg = self.primary_sh.execut_db_sql(
            sql_cmd, sql_type=f' -U {self.user_name}_audit '
            f'-W {macro.PASSWD_REPLACE}')
        self.logger.info(msg)
        self.assertNotIn(self.Constant.PERMISSION_DENIED, msg,
                         "执行失败:" + text)

        text = '----step11:审计管理员用户查看用户历史表 expect:成功----'
        self.logger.info(text)
        sql_cmd = f'''select * from 
                blockchain.{self.schema_name}_{self.table_name}_hist;'''
        self.logger.info(sql_cmd)
        msg = self.primary_sh.execut_db_sql(
            sql_cmd, sql_type=f' -U {self.user_name}_audit '
            f'-W {macro.PASSWD_REPLACE}')
        self.logger.info(msg)
        self.assertNotIn(self.Constant.PERMISSION_DENIED, msg,
                         "执行失败:" + text)

        text = '----step12:审计管理员用户查看全局区块表 expect:成功----'
        self.logger.info(text)
        sql_cmd = f'''select * from gs_global_chain;'''
        self.logger.info(sql_cmd)
        msg = self.primary_sh.execut_db_sql(
            sql_cmd, sql_type=f' -U {self.user_name}_audit '
            f'-W {macro.PASSWD_REPLACE}')
        self.logger.info(msg)
        self.assertNotIn(self.Constant.PERMISSION_DENIED, msg,
                         "执行失败:" + text)

        text = '----step13:系统管理员创建tablespace; expect:成功----'
        self.logger.info(text)
        sql_cmd = f'''
            create tablespace {self.ts_name} owner {self.user_name}_noruser 
            relative location 'hdfs_tablespace/hdfs_tablespace_1';'''
        self.logger.info(sql_cmd)
        msg = self.primary_sh.execut_db_sql(sql_cmd)
        self.logger.info(msg)
        self.assertIn(self.Constant.TABLESPCE_CREATE_SUCCESS, msg,
                      "执行失败:" + text)

        text = '----step14:普通用户修改防篡改表的tablespace expect:失败----'
        self.logger.info(text)
        sql_cmd = f'''
            alter table {self.schema_name}.{self.table_name}  
            set tablespace {self.ts_name};'''
        self.logger.info(sql_cmd)
        msg = self.primary_sh.execut_db_sql(
            sql_cmd, sql_type=f' -U {self.user_name}_noruser '
            f'-W {macro.PASSWD_REPLACE}')
        self.logger.info(msg)
        self.assertIn(self.Constant.PERMISSION_DENIED, msg,
                         "执行失败:" + text)

    def tearDown(self):
        text = '----step15:清理环境 expect:成功----'
        self.logger.info(text)
        sql_cmd = self.primary_sh.execut_db_sql(f'''
            drop table {self.schema_name}.{self.table_name} cascade;
            drop schema {self.schema_name} cascade;
            drop tablespace {self.ts_name};''')
        self.logger.info(sql_cmd)
        self.logger.info('删除用户')
        drop_msg = []
        for user in self.user_privileges.keys():
            drop_cmd = f"drop user if exists {user} cascade; "
            msg = self.primary_sh.execut_db_sql(drop_cmd)
            self.logger.info(msg)
            drop_msg.append(msg)
        self.assertEqual(3,
                         drop_msg.count(self.Constant.DROP_ROLE_SUCCESS_MSG),
                         '执行失败:' + text)
        self.assertIn(self.Constant.DROP_SCHEMA_SUCCESS_MSG, sql_cmd,
                      "执行失败:" + text)
        self.assertIn(self.Constant.DROP_TABLE_SUCCESS, sql_cmd,
                      "执行失败:" + text)
        self.assertIn(self.Constant.TABLESPCE_DROP_SUCCESS, sql_cmd,
                      "执行失败:" + text)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
